package com.pengtu.dao.detect;

import java.io.Serializable;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pengtu.dao.hibernate.HibernateDao;
import com.pengtu.entity.detect.Materialspot;
import com.pengtu.utils.DateUtils;

@Repository
public class MaterialspotDao extends HibernateDao<Materialspot, Serializable> {
	public void save(Materialspot entity) {
		if (StringUtils.isEmpty(entity.getId())) {
			entity.setId(null);
			entity.setCreateDate(DateUtils.getCurrentDate());
		}
		super.save(entity);
	}

	public List<Materialspot> select(String batch, String projectId,
			String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908)) from Materialspot u where 1 = 1";
		if (StringUtils.isNotEmpty(batch)) {
			hql += " and u.spotReport.batch = '" + batch + "'";
		}

		if (StringUtils.isNotEmpty(projectId)) {
			hql += " and u.projectId = '" + projectId + "'";
		}
		if (StringUtils.isNotEmpty(startTime)) {
			hql += " and u.reportdate >= to_date('" + startTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			hql += " and u.reportdate < to_date('" + endTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(projectId)) {
			hql += " group by u.projectId ";
		}
		return this.find(hql);
	}

	public List<Materialspot> selectFen(String socialLevel, String techGrade,
			String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908)) from Materialspot u where 1 = 1";
		if (StringUtils.isNotEmpty(socialLevel)) {
			hql += " and u.spotReport.projectInfo.socialLevel = '"
					+ socialLevel + "'";
		}

		if (StringUtils.isNotEmpty(techGrade)) {
			hql += " and u.spotReport.projectInfo.techGrade = '" + techGrade
					+ "'";
		}
		if (StringUtils.isNotEmpty(startTime)) {
			hql += " and u.reportdate >= to_date('" + startTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			hql += " and u.reportdate < to_date('" + endTime
					+ "','yyyy-MM-dd')";
		}

		// hql += " group by u.projectId ";

		return this.find(hql);
	}

	public List<Materialspot> selectFenT(String socialLevel, String techGrade,
			String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908)) from Materialspot u where 1 = 1";

		if(StringUtils.isNotEmpty(socialLevel)){
			hql += " and u.spotReport.projectInfo.socialLevel = '" + socialLevel + "'";
		}
		
		if(StringUtils.isNotEmpty(techGrade)){
			hql += " and u.spotReport.projectInfo.techGrade not in (" + techGrade +")";
		}
		
		if (StringUtils.isNotEmpty(startTime)) {
			hql += " and u.reportdate >= to_date('" + startTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			hql += " and u.reportdate < to_date('" + endTime
					+ "','yyyy-MM-dd')";
		}

		// hql += " group by u.projectId ";

		return this.find(hql);
	}
	
	public List<Materialspot> selectFenW(String socialLevel, String techGrade,
			String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908)) from Materialspot u where 1 = 1";
		if(StringUtils.isNotEmpty(socialLevel)){
			hql += " and u.spotReport.projectInfo.socialLevel not in (" + socialLevel + ")";
		}
		
		if(StringUtils.isNotEmpty(techGrade)){
			hql += " and u.spotReport.projectInfo.techGrade = '" + techGrade + "'";
		}
		
		if (StringUtils.isNotEmpty(startTime)) {
			hql += " and u.reportdate >= to_date('" + startTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			hql += " and u.reportdate < to_date('" + endTime
					+ "','yyyy-MM-dd')";
		}

		// hql += " group by u.projectId ";

		return this.find(hql);
	}
	
	public List<Materialspot> selectFenF(String socialLevel, String techGrade,
			String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908)) from Materialspot u where 1 = 1";
		if(StringUtils.isNotEmpty(socialLevel)){
			hql += " and u.spotReport.projectInfo.socialLevel not in (" + socialLevel + ")";
		}
		
		if(StringUtils.isNotEmpty(techGrade)){
			hql += " and u.spotReport.projectInfo.techGrade not in (" + techGrade +")";
		}
		
		if (StringUtils.isNotEmpty(startTime)) {
			hql += " and u.reportdate >= to_date('" + startTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			hql += " and u.reportdate < to_date('" + endTime
					+ "','yyyy-MM-dd')";
		}

		// hql += " group by u.projectId ";

		return this.find(hql);
	}

	public List<Materialspot> selectJidu(String startTime, String endTime, String planTypes, String townItems, String roadItems, String levelItems) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908)) from Materialspot u where 1 = 1";
		if (StringUtils.isNotEmpty(startTime)) {
			hql += " and u.reportdate >= to_date('" + startTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			hql += " and u.reportdate < to_date('" + endTime
					+ "','yyyy-MM-dd')";
		}

		if(StringUtils.isNotEmpty(townItems)){
			hql += "and (";
			String[] town = townItems.split(",");
			for (int i = 0; i < town.length; i++) {
				if(i == 0){
					hql += " u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}else{
					hql += " or u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}
			}
			hql += ")";
		}
		if(StringUtils.isNotEmpty(planTypes)){
			hql += " and u.spotReport.projectInfo.planType in (" + planTypes + ")";
		}
		
		if(StringUtils.isNotEmpty(roadItems)){
			hql += " and u.spotReport.projectInfo.techGrade in (" + roadItems + ")";
		}

		if(StringUtils.isNotEmpty(levelItems)){
			hql += " and u.spotReport.projectInfo.socialLevel in ('" + levelItems + "')";
		}
		return this.find(hql);
	}

	public List<Materialspot> selectJiduProj(String startTime, String endTime,
			String planTypes, String townItems, String roadItems,
			String levelItems) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908),u.projectId) from Materialspot u where 1 = 1";
		if (StringUtils.isNotEmpty(startTime)) {
			hql += " and u.reportdate >= to_date('" + startTime
					+ "','yyyy-MM-dd')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			hql += " and u.reportdate < to_date('" + endTime
					+ "','yyyy-MM-dd')";
		}

		if(StringUtils.isNotEmpty(townItems)){
			hql += "and (";
			String[] town = townItems.split(",");
			for (int i = 0; i < town.length; i++) {
				if(i == 0){
					hql += " u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}else{
					hql += " or u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}
			}
			hql += ")";
		}
		if(StringUtils.isNotEmpty(planTypes)){
			hql += " and u.spotReport.projectInfo.planType in (" + planTypes + ")";
		}
		
		if(StringUtils.isNotEmpty(roadItems)){
			hql += " and u.spotReport.projectInfo.techGrade in (" + roadItems + ")";
		}

		if(StringUtils.isNotEmpty(levelItems)){
			hql += " and u.spotReport.projectInfo.socialLevel in ('" + levelItems + "')";
		}
		 hql += " group by u.projectId ";
		return this.find(hql);
	}

	public List<Materialspot>  selectViewPro(String startTime, String endTime,
			String biaoduan, String projectId) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908)) from Materialspot u where 1 = 1";
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(biaoduan)){
			hql += " and u.spotReport.signid = '" + biaoduan + "'";
		}
		
		if(StringUtils.isNotEmpty(projectId)){
			hql += " and u.projectId = '" + projectId +"'";
		}
		return this.find(hql);
	}

	public List<Materialspot> selectByGroupby(String startTime, String endTime,
			String planTypes, String townItems, String roadItems,
			String levelItems, String groupBy) {
		String hql = "select new com.pengtu.entity.detect.Materialspot("
				+ "sum(u.countF901),sum(u.passF901),sum(u.countF902),sum(u.passF902),"
				+ "sum(u.countF903),sum(u.passF903),sum(u.countF904),sum(u.passF904),"
				+ "sum(u.countF905),sum(u.passF905),sum(u.countF906),sum(u.passF906),"
				+ "sum(u.countF907),sum(u.passF907),sum(u.countF908),sum(u.passF908),"+groupBy+") from Materialspot u where 1 = 1";
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(townItems)){
			hql += " and u.spotReport.projectInfo.atTown in ('" + townItems + "')";
		}
		
		if(StringUtils.isNotEmpty(planTypes)){
			hql += " and u.spotReport.projectInfo.planType in (" + planTypes + ")";
		}
		
		if(StringUtils.isNotEmpty(roadItems)){
			hql += " and u.spotReport.projectInfo.techGrade in (" + roadItems + ")";
		}

		if(StringUtils.isNotEmpty(levelItems)){
			hql += " and u.spotReport.projectInfo.socialLevel in ('" + levelItems + "')";
		}
		
		hql +=  " group by " + groupBy;
		return this.find(hql);
	}
	
}
